MySQL User Password Expired: how to (permanently) fix it |
您所在的位置：网站首页 › uiuc password expire › MySQL User Password Expired: how to (permanently) fix it |
MySQL User Password Expired: how to (permanently) fix it
Table of Contents How to reset an expired root passwordHow to disable the automatic password expiration policyIf you stumbled upon this post it probably means that your favourite MySQL management client (SQLyog, MySQL Workbench or any other) and/or your MySQL-based website or service is suddently unable to connect to your database server, leaving you with the following error message: Your password has expired. To log in you must change it using a client that supports expired passwords. This is a rather normal behaviour from MySQL 5.7.4 and above, where the automatic password expiration policy is enabled by default with a 360 days threshold - meaning that all passwords will expire once a year. To read more about this, we strongly suggest to read the official MySQL 5.7 documentation by clicking here. In a standard scenario, fixing such issue can be rather easy: you just have to login with an administrative user - such as the local root user - and change the expired password accordingly: needless to say, if the credentials are stored within a script (or website) configuration file, you will also need to change the password there as well. However, there are at least two scenarios that require further actions to take: If the root user password is also expired. If we want to prevent such issue from happening again.The purpose of this post is to give a viable answer for both of them. How to reset an expired root passwordAmong a wide number of available workarounds, I strongly suggest using the following (it works on Windows and Linux systems): Stop the MySQL service/process. Locate and open the MySQL configuration file used by your running MySQL instance (usually C:\ProgramData\MySQL\MySQL Server 5.7\my.ini in Windows, /etc/mysql/my.cnf or /etc/mysql/my.cnf under Linux). Add the following lines just below the [mysqld] section: INI 123 # Uncomment the following line to disable password check. # WARNING: this will allow any user to login, even with a wrong or expired password!!!skip-grant-tables Restart the MySQL service/process. Login to the server using your favourite MySQL GUI client with the root user - or any other administrative user - using any password (or even no passwords at all). Execute the following query: UPDATE mysql.user SET authentication_string = PASSWORD('YourPassword'), password_expired = 'N' WHERE User = 'YourUser' AND Host = 'YourHost'; FLUSH PRIVILEGES; Don't forget to change the YourPassword YourUser and YourHost placeholders with your choosen password, user and user's host (which is usually * or localhost ). Note that you can even change the root@localhost account's password there, in which case you need to specify User = 'root' AND Host = 'localhost' . Open the MySQL configuration file again and comment-out the skip-grant-tables command added moments ago. Stop and restart the MySQL service/process.That's it! If you don't want to alter the configuration file, you can just stop the mysqld server/process and then launch it again adding the --skip-grant-tables option. If you have no MySQL clients available, you can also do the latest 4 steps from the command prompt by using the mysql command, which is available in the /bin/ folder. Just type mysql -u root to access the mysql prompt: no password is necessary because the server was started with the --skip-grant-tables option. How to disable the automatic password expiration policyThe following query can be used to disable the automatic password expiration for a specific user: MySQL 1 ALTER USER 'root'@'localhost' PASSWORD EXPIRE NEVER;To establish a global policy such that passwords never expire, you can add (or change) this option to your MySQL configuration file: INI 12 [mysqld]default_password_lifetime=0IMPORTANT: you'll still need to manually set PASSWORD EXPIRE NEVER for every user created before the global policy change. That's all for now: happy MySQL!
 Print Friendly & PDF Download
|
【本文地址】
今日新闻 |
点击排行 |
|
推荐新闻 |
图片新闻 |
|
专题文章 |